SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [Live].[WBUAssetChange]
(
    @FundId AS VARCHAR (10),
		@EndDate AS DATE,
		@StartDate AS DATE,
		@ChgPct AS DECIMAL (38, 10)
      
) AS
BEGIN
DECLARE @StartExportId INT = (SELECT MAX(ExportId) FROM FSFundModel.Reporting.Export WHERE FundId = @FundId AND [NAVDate] = @StartDate)
DECLARE @EndExportId INT = (SELECT MAX(ExportId) FROM FSFundModel.Reporting.Export WHERE FundId = @FundId AND [NAVDate] = @EndDate)


;WITH EndActAssetsEnd AS (

		SELECT DISTINCT [a].[InstrumentId], [a].[TodayPrice] 
		, SUM([a].EndAdjAmt * [a].PurchPrc) OVER (PARTITION BY [a].[InstrumentId]) / NULLIF(SUM([a].EndAdjAmt) OVER	(PARTITION BY [a].[InstrumentId]),0) 	weightPurchPrc 
		FROM  [FSFundModel].[Reporting].[Asset] a 
		WHERE [a].[IsActiveEndOfDay] =1
		AND [a].[ExportId] = @EndExportId
),

BeginAssets AS (

		SELECT DISTINCT [a].[InstrumentId], [a].[TodayPrice]
			
		FROM  [FSFundModel].[Reporting].[Asset] a 
		WHERE [a].[IsActiveEndOfDay] =1
		AND [a].[ExportId] = @StartExportId


)
, joined AS (
		SELECT [ea].[InstrumentId], [ba].[TodayPrice] AS BeginPrice, [ea].[TodayPrice] AS EndPrice, [ea].weightPurchPrc FROM [EndActAssetsEnd] ea 
		LEFT JOIN	[BeginAssets] ba
		ON [ba].[InstrumentId] =[ea].[InstrumentId]
)
, change AS(

		SELECT 	
					[joined].[InstrumentId]
					, [i].[InstrName]
				, COALESCE( [joined].[BeginPrice],  [joined].weightPurchPrc	) BeginPrice
				, [joined].[EndPrice]
				, COALESCE([joined].[EndPrice] / NULLIF(COALESCE( [joined].[BeginPrice],  [joined].weightPurchPrc	) , 0) -1,0)  AS PrcChg

				FROM  [joined] 
				INNER JOIN  [FSFundModel].[dbo].[Instrument] i ON [i].[InstrumentId] = [joined].[InstrumentId]
)

SELECT * FROM [change]

WHERE [PrcChg] > @ChgPct
OR [PrcChg] < @ChgPct *-1

ORDER BY [PrcChg] DESC
END

GO
